"""
飞检数据STEP8筛选 - filte模块（串行执行版本）
============================================================================

从audit.py提取的filte部分，修改为串行执行，支持SQL超时监控。

主要功能：
1. 创建全局临时表 vlt_tmp_a, vlt_tmp_b, vlt_tmp_b2, vlt_tmp_b3, vlt_tmp_b4, vlt_tmp_b5
2. 创建VLT和VLT_LOG表
3. 串行执行每个ruleAct中的sqlList
4. 每个SQL执行超过TIMEOUT_LIMIT时自动跳过当前rule_act_id
5. 记录所有SQL执行情况到vlt_log表
6. 处理完成后删除临时表，创建同名普通表并保留数据

修改记录：
- 从并行改为串行执行
- 添加SQL超时监控机制
- 添加全局临时表创建
- 保留详细的执行日志记录
- 添加临时表转换为同名普通表功能
============================================================================
"""

import json
import re
import pandas as pd
pd.options.mode.chained_assignment = None  # 关闭警告
from sqlalchemy import create_engine, text
import os
import sys
import oracledb
import time
from datetime import datetime, timedelta
import threading
from threading import Lock

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from common.rule_flatten_handle import flatten_json, unflatten_json
from config import create_db_engine

# 超时常量设置 (秒)
TIMEOUT_LIMIT = 1500  # 30分钟超时

# 调试模式设置
DEBUG = False  # 设置为True时，有错误就中断

# 是否自动将 SELECT 结果追加写入 VLT（默认关闭，避免中途 SELECT 污染 VLT）
AUTO_APPEND_SELECT_TO_VLT = False

# 创建数据库引擎
engine = create_db_engine()

# 线程锁，用于同步数据库操作
db_lock = Lock()
progress_lock = Lock()

def print_inline(msg: str):
    """单行滚动显示进度信息"""
    try:
        with progress_lock:
            # 使用 \r 回到行首，清除当前行内容并打印新内容
            sys.stdout.write('\r' + ' ' * 120)  # 先清除当前行
            sys.stdout.write('\r' + msg[:120])  # 打印新内容，限制长度
            sys.stdout.flush()
    except Exception:
        # 回退为普通打印
        print(msg)

def print_newline(msg: str = ""):
    """输出新行信息"""
    with progress_lock:
        sys.stdout.write('\n' + msg + '\n')
        sys.stdout.flush()

def restart_db_session(reason: str | None = None):
    """重启数据库会话：
    - 关闭并释放现有连接池
    - 重新创建全新的引擎

    典型用途：
    - 对全局临时表(GTT)执行DDL（DROP/ALTER）时，当前会话曾使用过该GTT会报 ORA-14452，需要更换会话。
    - 释放潜在的锁或长事务，确保后续DDL顺利执行。
    """
    global engine
    try:
        msg = f"重启会话{(' - ' + reason) if reason else ''}，释放连接池..."
        print_newline(msg)
        # 释放现有连接
        engine.dispose()
        time.sleep(0.5)
    except Exception as e:
        print_newline(f"释放连接池时出现异常（忽略继续）：{e}")
    # 重新创建引擎
    engine = create_db_engine()
    print_newline("✓ 新会话已建立")

def format_time(seconds):
    """格式化时间显示"""
    if seconds < 60:
        return f"{seconds:.1f}秒"
    elif seconds < 3600:
        minutes = int(seconds // 60)
        secs = int(seconds % 60)
        return f"{minutes}分{secs}秒"
    else:
        hours = int(seconds // 3600)
        minutes = int((seconds % 3600) // 60)
        return f"{hours}时{minutes}分"

def create_global_temp_tables():
    """创建全局临时表 vlt_tmp_a, vlt_tmp_b, vlt_tmp_b2, vlt_tmp_b3, vlt_tmp_b4, vlt_tmp_b5"""
    print_newline("正在创建全局临时表...")
    
    # 定义临时表结构
    temp_table_ddl = """CREATE GLOBAL TEMPORARY TABLE {table_name} (
        d0_rowid VARCHAR2(255),
        setl_id VARCHAR2(100),
        psn_no VARCHAR2(100),
        hsp_abbr VARCHAR2(30),
        j_year CHAR(4),
        j_isin CHAR(1),
        in_time DATE,
        in_day DATE,
        out_time DATE,
        out_day DATE,
        period NUMBER,
        gnr_c NUMBER,
        gnr_b NUMBER,
        hifp_pay NUMBER,
        in_dept VARCHAR2(255),
        out_dept VARCHAR2(255),
        j_yearold NUMBER,
        age NUMBER,
        j_dayold_in NUMBER,
        j_dayold_out NUMBER,
        item_j_code VARCHAR2(255),
        item_j_name VARCHAR2(600),
        item_code VARCHAR2(600),
        item_name VARCHAR2(600),
        item_med_code VARCHAR2(255),
        item_hsp_code VARCHAR2(255),
        item_hsp_name VARCHAR2(600),        
        item_time DATE,
        item_day DATE,
        item_hour CHAR(2),
        item_dept VARCHAR2(255),
        apply_dept VARCHAR2(255),
        q NUMBER,
        p NUMBER,
        c NUMBER,
        b NUMBER,
        item_j_code2 VARCHAR2(4000),
        item_j_name2 VARCHAR2(4000),
        item_code2 VARCHAR2(4000),
        item_name2 VARCHAR2(4000),
        item_med_code2 VARCHAR2(4000),
        item_hsp_code2 VARCHAR2(4000),
        item_hsp_name2 VARCHAR2(4000),
        item_time2 VARCHAR2(4000),
        item_dept2 VARCHAR2(4000),
        apply_dept2 VARCHAR2(4000),
        q2 NUMBER,
        p2 VARCHAR2(4000),
        c2 NUMBER,
        b2 NUMBER,
        vlt_p NUMBER,
        vlt_q NUMBER,
        vlt_c NUMBER,
        vlt_b NUMBER,
        accu_q NUMBER,
        sum_q NUMBER,
        accu_day NUMBER,
        sum_day NUMBER,
        min10 NUMBER,
        w_day DATE,
        used_day_count NUMBER,
        tbl_info VARCHAR2(4000),
        related VARCHAR2(4000),
        str1 VARCHAR2(4000),
        str2 VARCHAR2(4000),
        str3 VARCHAR2(4000),
        str4 VARCHAR2(4000),
        str5 VARCHAR2(4000),
        str6 VARCHAR2(4000),
        str7 VARCHAR2(4000),
        str8 VARCHAR2(4000),
        str9 VARCHAR2(4000),
        num1 NUMBER,
        num2 NUMBER,
        num3 NUMBER,
        num4 NUMBER,
        num5 NUMBER,
        num6 NUMBER,
        num7 NUMBER,
        num8 NUMBER,
        num9 NUMBER,
        date1 DATE,
        date2 DATE,
        date3 DATE,
        date4 DATE,
        date5 DATE,
        date6 DATE,
        date7 DATE,
        date8 DATE,
        date9 DATE
    ) ON COMMIT PRESERVE ROWS"""
    
    temp_tables = ['vlt_tmp_a', 'vlt_tmp_b', 'vlt_tmp_b2', 'vlt_tmp_b3', 'vlt_tmp_b4', 'vlt_tmp_b5']
    
    with engine.connect() as conn:
        for idx, table_name in enumerate(temp_tables):
            try:
                print_inline(f"处理临时表 [{idx+1}/{len(temp_tables)}]: {table_name}")
                
                # 先尝试删除已存在的全局临时表
                drop_sql = f"""
                BEGIN
                    EXECUTE IMMEDIATE 'DROP TABLE {table_name}';
                EXCEPTION
                    WHEN OTHERS THEN
                        IF SQLCODE != -942 THEN -- -942 是表不存在的错误代码
                            NULL; -- 忽略其他错误
                        END IF;
                END;
                """
                conn.execute(text(drop_sql))
                conn.commit()  # 立即提交删除操作
                
                # 创建新的临时表
                create_sql = temp_table_ddl.format(table_name=table_name)
                conn.execute(text(create_sql))
                conn.commit()  # 立即提交创建操作
                
                print_inline(f"临时表 [{idx+1}/{len(temp_tables)}]: {table_name} 创建成功")
                
            except Exception as e:
                print_newline(f"创建临时表失败: {table_name}, 错误: {e}")
                if DEBUG:
                    raise e
                else:
                    # 非DEBUG模式下尝试继续
                    pass
    
    print_newline("✓ 所有全局临时表创建完成")

def convert_temp_tables_to_permanent():
    """将全局临时表转换为普通表以便调试"""
    print_newline("正在将全局临时表转换为普通表...")
    
    # 定义普通表结构（与全局临时表相同结构，但去掉GLOBAL TEMPORARY和ON COMMIT子句）
    permanent_table_ddl = """CREATE TABLE {table_name} (
        d0_rowid VARCHAR2(255),
        setl_id VARCHAR2(100),
        psn_no VARCHAR2(100),
        hsp_abbr VARCHAR2(30),
        j_year CHAR(4),
        j_isin CHAR(1),
        in_time DATE,
        in_day DATE,
        out_time DATE,
        out_day DATE,
        period NUMBER,
        gnr_c NUMBER,
        gnr_b NUMBER,
        hifp_pay NUMBER,
        in_dept VARCHAR2(255),
        out_dept VARCHAR2(255),
        j_yearold NUMBER,
        age NUMBER,
        j_dayold_in NUMBER,
        j_dayold_out NUMBER,
        item_j_code VARCHAR2(255),
        item_j_name VARCHAR2(600),
        item_code VARCHAR2(600),
        item_name VARCHAR2(600),
        item_med_code VARCHAR2(255),
        item_hsp_code VARCHAR2(255),
        item_hsp_name VARCHAR2(600),        
        item_time DATE,
        item_day DATE,
        item_hour CHAR(2),
        item_dept VARCHAR2(255),
        apply_dept VARCHAR2(255),
        q NUMBER,
        p NUMBER,
        c NUMBER,
        b NUMBER,
        item_j_code2 VARCHAR2(4000),
        item_j_name2 VARCHAR2(4000),
        item_code2 VARCHAR2(4000),
        item_name2 VARCHAR2(4000),
        item_med_code2 VARCHAR2(4000),
        item_hsp_code2 VARCHAR2(4000),
        item_hsp_name2 VARCHAR2(4000),
        item_time2 VARCHAR2(4000),
        item_dept2 VARCHAR2(4000),
        apply_dept2 VARCHAR2(4000),
        q2 NUMBER,
        p2 VARCHAR2(4000),
        c2 NUMBER,
        b2 NUMBER,
        vlt_p NUMBER,
        vlt_q NUMBER,
        vlt_c NUMBER,
        vlt_b NUMBER,
        accu_q NUMBER,
        sum_q NUMBER,
        accu_day NUMBER,
        sum_day NUMBER,
        min10 NUMBER,
        w_day DATE,
        used_day_count NUMBER,
        tbl_info VARCHAR2(4000),
        related VARCHAR2(4000),
        str1 VARCHAR2(4000),
        str2 VARCHAR2(4000),
        str3 VARCHAR2(4000),
        str4 VARCHAR2(4000),
        str5 VARCHAR2(4000),
        str6 VARCHAR2(4000),
        str7 VARCHAR2(4000),
        str8 VARCHAR2(4000),
        str9 VARCHAR2(4000),
        num1 NUMBER,
        num2 NUMBER,
        num3 NUMBER,
        num4 NUMBER,
        num5 NUMBER,
        num6 NUMBER,
        num7 NUMBER,
        num8 NUMBER,
        num9 NUMBER,
        date1 DATE,
        date2 DATE,
        date3 DATE,
        date4 DATE,
        date5 DATE,
        date6 DATE,
        date7 DATE,
        date8 DATE,
        date9 DATE
    )"""
    
    temp_tables = ['vlt_tmp_a', 'vlt_tmp_b', 'vlt_tmp_b2', 'vlt_tmp_b3', 'vlt_tmp_b4', 'vlt_tmp_b5']
    
    def _safe_drop_gtt(name: str) -> bool:
        """在独立连接中安全删除全局临时表。
        处理：
        - ORA-01418/ORA-00942 表不存在 -> 视为成功
        - ORA-14452 当前会话使用过GTT -> 重启会话后再试一次
        - ORA-00054 资源忙 -> 等待重试最多3次
        返回：是否成功删除
        """
        # 尝试多次（资源忙情况下）
        max_attempts = 3
        attempt = 1
        while attempt <= max_attempts:
            try:
                with engine.connect() as c:
                    c.execute(text(f"DROP TABLE {name}"))
                    c.commit()
                return True
            except Exception as e:
                msg = str(e)
                low = msg.lower()
                if 'ora-00942' in low or 'table or view does not exist' in low:
                    return True
                if 'ora-14452' in low:
                    # 当前会话使用过该GTT，需更换会话
                    restart_db_session(f"删除全局临时表 {name} 遇到 ORA-14452")
                    # 重试（不增加 attempt 计数）
                    attempt += 1
                    continue
                if 'ora-00054' in low or 'resource busy' in low:
                    time.sleep(1.0 * attempt)
                    attempt += 1
                    continue
                # 其他错误
                print_newline(f"DROP {name} 失败：{e}")
                return False
        return False

    for idx, table_name in enumerate(temp_tables):
        try:
            print_inline(f"转换临时表 [{idx+1}/{len(temp_tables)}]: {table_name} -> 普通表")

            # 步骤2: 尝试删除原来的全局临时表（必要时自动重启会话并重试）
            dropped = _safe_drop_gtt(table_name)
            if not dropped:
                # 作为兜底：如果确实无法删除，则改用新名字创建普通表，避免阻塞整体流程
                normal_name = f"{table_name}_PERM"
                print_newline(f"警告：无法删除 {table_name}，改为创建普通表 {normal_name}")
                create_target = normal_name
            else:
                create_target = table_name

            # 步骤3: 创建普通表（同名或 _PERM 后缀）
            with engine.connect() as c2:
                create_sql = permanent_table_ddl.format(table_name=create_target)
                c2.execute(text(create_sql))
                c2.commit()

            print_inline(f"普通表 [{idx+1}/{len(temp_tables)}]: {create_target} 创建成功 ")

        except Exception as e:
            print_newline(f"转换临时表失败: {table_name}, 错误: {e}")
            if DEBUG:
                raise e
            # 非DEBUG模式下继续处理后续表
    
    print_newline("✓ 全局临时表转换为普通表完成")

def create_vlt_tables():
    """创建VLT和VLT_LOG表"""
    print_newline("正在创建VLT和VLT_LOG表...")
    
    # ============ 创建 VLT 表 ============
    sqlCheckVlt = """
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE VLT PURGE';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN -- -942 是表不存在的错误代码
                RAISE;
            END IF;
    END;
    """
    sqlCreateVlt = """CREATE TABLE VLT (
      D0_ROWID VARCHAR2(255),
      ITEM_TIME_SEQ NUMBER,
      SETL_ID VARCHAR2(100),
      PSN_NO VARCHAR2(100),
      HSP_ABBR VARCHAR2(30),
      J_YEAR CHAR(4),
      J_ISIN CHAR(1),
      ITEM_J_CODE VARCHAR2(255),
      ITEM_J_NAME VARCHAR2(600),
      ITEM_TIME DATE,
      ITEM_DAY DATE,
      ITEM_HOUR CHAR(2),
      Q NUMBER,
      P NUMBER,
      C NUMBER,
      B NUMBER,
      ITEM_J_CODE2 VARCHAR2(4000),
      ITEM_J_NAME2 VARCHAR2(4000),
      ITEM_CODE2 VARCHAR2(4000),
      ITEM_NAME2 VARCHAR2(4000),
      ITEM_MED_CODE2 VARCHAR2(4000),
      ITEM_HSP_CODE2 VARCHAR2(4000),
      ITEM_HSP_NAME2 VARCHAR2(4000),
      ITEM_TIME2 VARCHAR2(4000),
      ITEM_DEPT2 VARCHAR2(4000),
      APPLY_DEPT2 VARCHAR2(4000),
      Q2 NUMBER,
      P2 VARCHAR2(4000),
      C2 NUMBER,
      B2 NUMBER,
      RULE_ID VARCHAR2(10),
      RULE_ACT_ID VARCHAR2(50),
      RULE_ACT_MSG VARCHAR2(4000),
      MSG VARCHAR2(4000),
      RELATED VARCHAR2(4000),
      RELATED_ROWID VARCHAR2(4000),
      VLT_Q NUMBER,
      VLT_P NUMBER,
      VLT_C NUMBER,
      VLT_B NUMBER
    )"""
    
    # ============ 创建 VLT_LOG 表 ============
    sqlCheckVltLog = """
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE VLT_LOG PURGE';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN -- -942 是表不存在的错误代码
                RAISE;
            END IF;
    END;
    """
    sqlCreateVltLog = """CREATE TABLE VLT_LOG (
        HSP_ABBR VARCHAR2(30),
        RULE_ID VARCHAR2(10),
        RULE_ACT_ID VARCHAR2(50),
        SQL CLOB,
        SQL_ORD NUMBER,
        STATUS VARCHAR2(10),
        ERROR CLOB,
        TIME NUMBER,
        RESULT_LEN NUMBER
    )"""
    
    with engine.connect() as conn:
        # 创建VLT表
        print_inline("创建VLT表...")
        try:
            conn.execute(text(sqlCheckVlt))
        except:
            pass
        
        conn.execute(text(sqlCreateVlt))
        
        # 创建VLT_LOG表
        print_inline("创建VLT_LOG表...")
        try:
            conn.execute(text(sqlCheckVltLog))
        except:
            pass
        
        conn.execute(text(sqlCreateVltLog))
        
        conn.commit()
        
    print_newline("✓ VLT和VLT_LOG表创建成功")

def create_vlt_indexes():
    """为VLT表创建索引"""
    print_newline("正在为VLT表创建索引...")
    
    indexes = [
        "CREATE INDEX idx_vlt_d0_rowid ON VLT (D0_ROWID)",
        "CREATE INDEX idx_vlt_setl_id ON VLT (SETL_ID)",
        "CREATE INDEX idx_vlt_psn_no ON VLT (PSN_NO)",
        "CREATE INDEX idx_vlt_hsp_abbr ON VLT (HSP_ABBR)",
        "CREATE INDEX idx_vlt_j_year ON VLT (J_YEAR)",
        "CREATE INDEX idx_vlt_j_isin ON VLT (J_ISIN)",
        "CREATE INDEX idx_vlt_item_j_code ON VLT (ITEM_J_CODE)",
        "CREATE INDEX idx_vlt_item_day ON VLT (ITEM_DAY)",
        "CREATE INDEX idx_vlt_item_hour ON VLT (ITEM_HOUR)",
        "CREATE INDEX idx_vlt_rule_id ON VLT (RULE_ID)",
        "CREATE INDEX idx_vlt_rule_act_id ON VLT (RULE_ACT_ID)"
    ]
    
    with engine.connect() as conn:
        for idx, index_sql in enumerate(indexes):
            try:
                index_name = index_sql.split('CREATE INDEX ')[1].split(' ON')[0]
                print_inline(f"创建索引 [{idx+1}/{len(indexes)}]: {index_name}")
                conn.execute(text(index_sql))
            except Exception as e:
                print_newline(f"创建索引失败: {index_sql}, 错误: {e}")
                if DEBUG:
                    raise e
        
        conn.commit()
    
    print_newline("✓ VLT表索引创建完成")

def log_sql_execution(hsp_abbr, rule_id, rule_act_id, sql, sql_ord, status, error, execution_time, result_len, conn=None):
    """记录SQL执行情况到vlt_log表 (不自动提交)"""
    try:
        if conn is None:
            # 如果没有传入连接，创建新连接
            with engine.connect() as new_conn:
                _log_to_database(new_conn, hsp_abbr, rule_id, rule_act_id, sql, sql_ord, status, error, execution_time, result_len)
                new_conn.commit()
        else:
            # 使用传入的连接，并且提交
            _log_to_database(conn, hsp_abbr, rule_id, rule_act_id, sql, sql_ord, status, error, execution_time, result_len)
            conn.commit()
    except Exception as e:
        raise Exception(f"VLT_LOG插入失败: {e}")

def _log_to_database(conn, hsp_abbr, rule_id, rule_act_id, sql, sql_ord, status, error, execution_time, result_len):
    """实际的数据库日志插入操作"""
    raw_conn = conn.connection.dbapi_connection
    cursor = raw_conn.cursor()
    
    sqlLogIns = """INSERT INTO VLT_LOG (hsp_abbr, rule_id, rule_act_id, sql, sql_ord, status, error, time, result_len)
                    VALUES (:hsp_abbr, :rule_id, :rule_act_id, :sql, :sql_ord, :status, :error, :time, :result_len)"""
    
    cursor.setinputsizes(
        hsp_abbr=30, rule_id=10, rule_act_id=50,
        sql=oracledb.CLOB, status=10, error=oracledb.CLOB,
        time=None, result_len=None
    )
    
    with db_lock:
        cursor.execute(sqlLogIns, {
            'hsp_abbr': hsp_abbr,
            'rule_id': rule_id,
            'rule_act_id': rule_act_id,
            'sql': sql,
            'sql_ord': sql_ord,
            'status': status,
            'error': error,
            'time': execution_time,
            'result_len': result_len
        })
        # 在原生连接上提交事务
        raw_conn.commit()
    cursor.close()

def execute_sql_with_timeout(sql, sql_ord, hsp_abbr, rule_id, rule_act_id, log_conn=None, data_conn=None, timeout_seconds=TIMEOUT_LIMIT):
    """执行SQL并支持超时监控"""
    start_time = time.time()
    
    class SQLExecutor:
        def __init__(self):
            self.result = None
            self.error = None
            self.completed = False
            
        def run_sql(self):
            try:
                if data_conn is None:
                    # 如果没有提供数据连接，创建独立连接（向后兼容）
                    with engine.connect() as conn:
                        result = conn.execute(text(sql))
                        self._process_result(result)
                        conn.commit()  # 独立连接立即提交
                else:
                    # 使用提供的数据连接（支持事务）
                    result = data_conn.execute(text(sql))
                    self._process_result(result)
                    # 不在这里提交，由调用者控制事务
                        
                self.completed = True
            except Exception as e:
                self.error = str(e)
                self.completed = True
                if DEBUG:
                    raise e
                    
        def _process_result(self, result):
            # 检查SQL类型，只有SELECT语句才需要fetchall()
            sql_upper = sql.strip().upper()
            if sql_upper.startswith('SELECT'):
                # SELECT语句，获取返回的行数据
                rows = result.fetchall()
                fields = result.keys()
                resultData = [dict(zip(fields, row)) for row in rows]
                self.result = resultData
            else:
                # INSERT/UPDATE/DELETE/CREATE等语句，直接使用受影响的行数
                affected_rows = getattr(result, 'rowcount', 0)
                # 直接将受影响的行数设置为结果，用于result_len计算
                self.result = affected_rows
    
    executor = SQLExecutor()
    thread = threading.Thread(target=executor.run_sql)
    thread.daemon = True
    thread.start()
    
    # 等待SQL执行完成或超时
    thread.join(timeout=timeout_seconds)
    
    execution_time = time.time() - start_time
    
    if thread.is_alive():
        # 超时
        error_msg = f"SQL执行超时({timeout_seconds}秒)"
        log_sql_execution(hsp_abbr, rule_id, rule_act_id, sql, sql_ord, 'timeout', error_msg, execution_time, None, log_conn)
        return None, error_msg, execution_time
    elif executor.error:
        # 执行错误
        log_sql_execution(hsp_abbr, rule_id, rule_act_id, sql, sql_ord, 'error', executor.error, execution_time, None, log_conn)
        return None, executor.error, execution_time
    else:
        # 执行成功
        if isinstance(executor.result, list):
            # SELECT语句返回列表
            result_len = len(executor.result)
        else:
            # INSERT/UPDATE/DELETE等语句返回受影响的行数
            result_len = executor.result if executor.result else 0
        
        log_sql_execution(hsp_abbr, rule_id, rule_act_id, sql, sql_ord, 'success', None, execution_time, result_len, log_conn)
        
        # 如果有结果数据，且开启了自动写入开关，则插入到VLT表（仅用于特定场景）
        if AUTO_APPEND_SELECT_TO_VLT and isinstance(executor.result, list) and len(executor.result) > 0:
            try:
                df = pd.DataFrame(executor.result)
                with db_lock:
                    df.to_sql('vlt', con=engine, if_exists='append', index=False)
            except Exception as e:
                if DEBUG:
                    raise Exception(f"插入VLT表失败: {e}")
                else:
                    pass  # 非DEBUG模式下忽略VLT插入错误
        
        return executor.result, None, execution_time

def process_hospital_filte(hsp_abbr):
    """处理单个医院的筛选任务"""
    print_newline(f"开始处理医院: {hsp_abbr}")
    
    # 读取医院的所有规则ID
    with engine.connect() as conn:
        sqlFetchRules = text("""
            SELECT DISTINCT rule_id FROM rule_apply WHERE hsp_abbr = :hsp_abbr
        """)
        result = conn.execute(sqlFetchRules, {'hsp_abbr': hsp_abbr})
        ruleIdList = [row[0] for row in result.fetchall()]
    
    if not ruleIdList or len(ruleIdList) == 0:
        raise Exception(f"医院 {hsp_abbr} 没有配置任何规则")
    
    hospital_start_time = time.time()
    total_rules = len(ruleIdList)
    processed_rules = 0
    skipped_rule_acts = 0
    successful_sqls = 0
    failed_sqls = 0
    timeout_sqls = 0
    
    for rule_idx, rule_id in enumerate(ruleIdList):
        # 从数据库获取 当前 rule_id 下的 ruleActIdList
        with engine.connect() as conn:
            # 注意python的字符串格式化需要双百分号 %%
            sqlFetchRuleActId = text("""
                SELECT DISTINCT key, value FROM rule_apply WHERE rule_id = :rule_id AND hsp_abbr = :hsp_abbr AND key like '%ruleActId' ORDER BY value
            """)
            result = conn.execute(sqlFetchRuleActId, {'rule_id': rule_id, 'hsp_abbr': hsp_abbr})
            # 提取 ruleActOrdlKey, ruleActId 列表
            ruleActData = [(row[0], row[1]) for row in result.fetchall()]
        
        for rule_act_id in ruleActData:
            # rule_act_ord = rule_act_id.split('_')[1]
            ruleActOrdKey = rule_act_id[0]
            rule_act_id = rule_act_id[1]

            ruleActSqlListKey = ruleActOrdKey.replace('ruleActId', 'sqlList') 

            # 从数据库获取 当前 rule_id 和 rule_act_id 下的 sqlList
            with engine.connect() as conn:
                # 取出 key 与 sql，并在 Python 侧按数字序排序，避免 '...#10' 排在 '...#2' 前面的字典序问题
                sqlFetchSqlList = text("""
                    SELECT key, sql FROM rule_apply 
                    WHERE rule_id = :rule_id AND hsp_abbr = :hsp_abbr AND key like :key_pattern
                """)
                key_pattern = f'{ruleActSqlListKey}#%'
                rows = conn.execute(sqlFetchSqlList, {'rule_id': rule_id, 'hsp_abbr': hsp_abbr, 'key_pattern': key_pattern}).fetchall()

                def _ord_from_key(k: str) -> int:
                    try:
                        # 取最后一个 '#' 之后的数字部分
                        return int(k.rsplit('#', 1)[-1])
                    except Exception:
                        return 0

                # 排序
                rows_sorted = sorted(rows, key=lambda r: _ord_from_key(r[0] or ''))

                # 正确处理CLOB字段
                sqlListRows = []
                for k, clob_value in rows_sorted:
                    if clob_value is not None:
                        if hasattr(clob_value, 'read'):
                            sql_content = clob_value.read()
                        else:
                            sql_content = str(clob_value)
                        sqlListRows.append(sql_content)
                sql_list = sqlListRows
            
            if not sql_list or len(sql_list) == 0:
                continue
            
            rule_act_start_time = time.time()
            rule_act_skipped = False
            
            # 为当前 ruleActId 创建日志连接和数据连接（注意：GTT 的 TRUNCATE 必须在与后续 SQL 相同会话里执行）
            with engine.connect() as log_conn, engine.connect() as data_conn:
                # 在每个 ruleAct 开始时清空全局临时表（同一 data_conn 会话）
                temp_tables = ['vlt_tmp_a', 'vlt_tmp_b', 'vlt_tmp_b2', 'vlt_tmp_b3', 'vlt_tmp_b4', 'vlt_tmp_b5']
                for table_name in temp_tables:
                    try:
                        data_conn.execute(text(f"TRUNCATE TABLE {table_name}"))
                    except Exception as e:
                        if DEBUG:
                            print_newline(f"警告: 清空临时表 {table_name} 失败: {e}")
                            raise e  # DEBUG模式下抛出异常
                        # 非DEBUG模式下忽略清空表的错误
                data_conn.commit()
                try:
                    # 按顺序执行sqlList中的所有SQL
                    for sql_idx, sql_item in enumerate(sql_list):
                        # 处理不同的SQL数据结构
                        sql = sql_item
                        
                        # 显示当前进度
                        elapsed_time = time.time() - hospital_start_time
                        progress_msg = (f"[{hsp_abbr}] 规则{rule_idx+1}/{total_rules} {rule_id} "
                                      f"SQL{sql_idx+1}/{len(sql_list)} "
                                      f"成功:{successful_sqls} 失败:{failed_sqls} 超时:{timeout_sqls} "
                                      f"用时:{format_time(elapsed_time)}")
                        print_inline(progress_msg)
                        
                        result, error, execution_time = execute_sql_with_timeout(
                            sql, sql_idx, hsp_abbr, rule_id, rule_act_id, log_conn, data_conn, TIMEOUT_LIMIT
                        )
                        
                        # 立即提交日志，确保日志记录不丢失
                        log_conn.commit() 
                        
                        if error:
                            if 'timeout' in error.lower():
                                timeout_sqls += 1
                            else:
                                failed_sqls += 1
                            
                            if DEBUG:
                                # DEBUG模式下，立即停止整个程序
                                print(f"\n[DEBUG模式] 检测到错误，立即停止程序: {error}")
                                data_conn.rollback()
                                data_conn.close()
                                log_conn.close()
                                sys.exit(1)
                            
                            # 任何错误都跳过当前 rule_act_id 的后续 SQL
                            rule_act_skipped = True
                            # 回滚当前 ruleAct 的数据变更
                            data_conn.rollback()
                            break
                        else:
                            successful_sqls += 1
                            # 立即提交数据变更，确保临时表数据对后续SQL可见
                            data_conn.commit()
                    
                    # ruleActId 执行完成（每个SQL已经单独提交了）
                    # if not rule_act_skipped:
                    #     data_conn.commit()  # 不需要再次提交
                    
                except Exception as e:
                    # 如果整个 ruleActId 执行出错，回滚数据但保留日志
                    data_conn.rollback()
                    # 日志应该保留，即使出现异常
                    log_conn.commit()
                    if DEBUG:
                        raise e  # DEBUG模式下立即抛出异常，停止执行
                    rule_act_skipped = True
            
            if rule_act_skipped:
                skipped_rule_acts += 1
        
        processed_rules += 1
    
    # 统计信息
    total_time = time.time() - hospital_start_time
    print_newline(f"✓ 医院 {hsp_abbr} 处理完成 - "
                  f"用时:{format_time(total_time)} "
                  f"规则:{processed_rules}/{total_rules} "
                  f"SQL执行统计: 成功{successful_sqls}, 失败{failed_sqls}, 超时{timeout_sqls}")

def main():
    """主函数"""
    print_newline("=" * 80)
    print_newline("飞检数据STEP8筛选 - filte模块 (串行执行版本)")
    print_newline("=" * 80)
    
    # 读取医院列表
    try:
        with open('hspList.json', 'r', encoding='utf-8') as file:
            hspList = json.load(file)
        hsp_abbr_list = [item['hsp_abbr'] for item in hspList if item['hsp_abbr']]
        print_newline(f"读取到 {len(hsp_abbr_list)} 个医院: {', '.join(hsp_abbr_list)}")
    except FileNotFoundError:
        print_newline("错误: 找不到 hspList.json 文件")
        return
    
    overall_start_time = time.time()
    
    try:
        # 步骤1: 创建全局临时表
        print_newline(f"步骤1: 创建全局临时表")
        create_global_temp_tables()
        
        # 步骤2: 创建VLT和VLT_LOG表
        print_newline(f"步骤2: 创建VLT和VLT_LOG表")
        create_vlt_tables()
        
        # 步骤3: 串行处理每个医院的筛选任务
        print_newline(f"步骤3: 串行处理筛选任务")
        for idx, hsp_abbr in enumerate(hsp_abbr_list):
            print_newline(f"处理医院 [{idx+1}/{len(hsp_abbr_list)}]: {hsp_abbr}")
            process_hospital_filte(hsp_abbr)
        
        # 步骤4: 创建VLT表索引
        print_newline(f"步骤4: 创建VLT表索引")
        create_vlt_indexes()
        
        # 步骤5: 将全局临时表转换为普通表（同时删除原临时表）
        print_newline(f"步骤5: 转换临时表为普通表")
        convert_temp_tables_to_permanent()
        
        # 完成
        total_time = time.time() - overall_start_time
        print_newline("")
        print_newline("=" * 80)
        print_newline("✓ 所有筛选任务处理完成！")
        print_newline(f"总用时: {format_time(total_time)}")
        print_newline(f"超时限制: {TIMEOUT_LIMIT}秒")
        print_newline("普通表已创建: vlt_tmp_a, vlt_tmp_b, vlt_tmp_b2, vlt_tmp_b3, vlt_tmp_b4, vlt_tmp_b5")
        print_newline("=" * 80)
        
    except Exception as e:
        print_newline(f"处理过程中发生错误: {e}")
        raise

if __name__ == "__main__":
    main()
